package com.css.fxfzaqbz.modules.qhmapfiles.repository;

import com.css.fxfzaqbz.util.PlatformDateUtils;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import com.css.fxfzaqbz.util.PlatformSessionUtils;
import com.css.fxfzaqbz.util.UUIDGenerator;
import com.css.fxfzaqbz.modules.qhmapfiles.basedivision.service.Impl.BaseDivisionServiceImpl;
import com.css.fxfzaqbz.modules.qhmapfiles.basedivision.vo.DivisionVO;
import com.css.fxfzaqbz.modules.qhmapfiles.repository.entity.QhMapFilesEntity;
import com.css.fxfzaqbz.modules.qhmapfiles.viewobjects.QhMapFilesQueryParams;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author zyt
 * @Date: 2022/09/26
 */
@Repository
@PropertySource("application-dev.yml")
public class QhMapFilesImport {
    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";

    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        //Class.forName("com.mysql.cj.jdbc.Driver");
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }

    @Autowired
    private BaseDivisionServiceImpl baseDivisionServiceImpl;

    /**
     * sql 批量插入
     *
     * @return
     */
    public int sqlBatchSaveEntities(List<QhMapFilesEntity> list) throws Exception {
        int count = 0;
        Connection conn = getConnection();
        //上传文件信息表
        String tableName = "qh_map_files";
        StringBuffer sql = new StringBuffer("insert into " + tableName + " (uuid, filepath, type, province, city, area, affiliated_file_name, date_str, postfix, is_save, quality_status, is_delete, create_user, create_time, quality_comment,code_type,data_length, province_code, city_code, area_code) "
                + "values ");
        //上传文件信息数据
        for (QhMapFilesEntity entity : list) {
            count++;
            String id = entity.getUuid();
            String filepath = entity.getFilepath();
            String type = entity.getType();//上传类型
            String province = entity.getProvince();
            String city = entity.getCity();
            String area = entity.getArea();
            String affiliatedFileName = entity.getAffiliatedFileName();
            String dateStr = entity.getDateStr();
            String postfix = entity.getPostfix();//文件类型
            String isSave = entity.getIsSave();//是否保存（0：未保存，1：已保存）
            String qualityStatus = entity.getQualityStatus();//上传省市级类型（0：省，1：市）
            String isDelete = entity.getIsDelete();//是否删除（0：未删除，1：已删除）
            String qualityComment = entity.getQualityComment();//质检意见
            String createUser = entity.getCreateUser();
            createUser = (createUser != null ? createUser : "");
            Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();
            String codeType = entity.getCodeType();//编码类型（0：UTF-8，1：GBK）
            int dataLength = entity.getDataLength();//数据长度
            //查询省市县行政区划代码
            DivisionVO divisionVO = new DivisionVO(province,city,area);
            //String provinceCode = baseDivisionServiceImpl.findDivisionCodeByName(divisionVO,"1");//省级行政区划代码
            //String cityCode = baseDivisionServiceImpl.findDivisionCodeByName(divisionVO,"2");//市级行政区划代码
            //String areaCode = baseDivisionServiceImpl.findDivisionCodeByName(divisionVO,"3");//区县级行政区划代码
            String provinceCode = "";//省级行政区划代码
            String cityCode = "";//市级行政区划代码
            String areaCode = "";//区县级行政区划代码


            sql.append("('" + id + "','" + filepath + "','" + type + "','" + province + "','" + city + "','" + area
                    +"','" + affiliatedFileName + "','" + dateStr + "','" + postfix + "','" + isSave + "','" + qualityStatus
                    + "','" + isDelete + "','" + createUser + "','" + createTime + "','" + qualityComment +
                    "','" + codeType + "'," + dataLength + ",'" + provinceCode + "','" + cityCode + "','" + areaCode + "'),");
        }
        String sql_str = sql.toString();
        sql_str = sql_str.substring(0, sql_str.length() - 1);
        PreparedStatement ps = conn.prepareStatement(sql_str);
        //ps.executeUpdate(sql_str);
        ps.executeUpdate();
        if (ps != null) ps.close();
        if (conn != null) conn.close();
        return count;
    }

    /**
     * sql 批量物理删除
     *
     * @return
     */
    public int sqlBatchDeleteEntities(List<QhMapFilesEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            Connection conn = getConnection();

            String tableName = "qh_map_files";

            StringBuffer sql = new StringBuffer("DELETE FROM " + tableName + " WHERE uuid IN ( ");
            //上传文件信息数据
            for (QhMapFilesEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            PreparedStatement ps = conn.prepareStatement(sql_str);
            //ps.executeUpdate(sql_str);
            ps.executeUpdate();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * sql 批量逻辑删除
     *
     * @return
     */
    public int sqlBatchLogicDeleteEntities(List<QhMapFilesEntity> list) {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();

            String tableName = "qh_map_files";

            StringBuffer sql = new StringBuffer("UPDATE " + tableName + " set is_delete = '1',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");
            //上传文件信息数据
            for (QhMapFilesEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            //ps.executeUpdate(sql_str);
            ps.executeUpdate();

        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    /**
     * sql 查询文件
     *
     * @return
     */
    public List<QhMapFilesEntity> searchMapFiles(QhMapFilesQueryParams queryParams) {

        List<QhMapFilesEntity> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();

            String tableName = "qh_map_files";

            StringBuffer sql = new StringBuffer("select * from " + tableName + " ");

            StringBuilder whereSql = new StringBuilder(" where 1=1 ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
                whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
                whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
                whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getAffiliatedFileName())) {
                whereSql.append(" and affiliated_file_name ='" + queryParams.getAffiliatedFileName() + "'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getIsSave())) {
                whereSql.append(" and is_save ='" + queryParams.getIsSave() + "'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getIsDelete())) {
                whereSql.append(" and is_delete = '" + queryParams.getIsDelete() + "'");
            }
            //上传类型（SITE_）
            if(!PlatformObjectUtils.isEmpty(queryParams.getType())) {
                whereSql.append(" and type = '" + queryParams.getType() + "'");
            }

            String sql_str = sql.append(whereSql.toString()).toString();
            ps = conn.prepareStatement(sql_str);
            rs = ps.executeQuery();
            while (rs.next()) {
                QhMapFilesEntity entity = new QhMapFilesEntity();
                String uuid = rs.getString("uuid");
                entity.setUuid(uuid);
                String filepath = rs.getString("filepath");
                entity.setFilepath(filepath);
                String name = rs.getString("affiliated_file_name");
                entity.setAffiliatedFileName(name);
                Date create_time = rs.getDate("create_time");
                entity.setCreateTime(create_time);
                result.add(entity);
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return result;
    }

    /**
     * sql 查询文件（默认未删除）
     *
     * @return
     */
    public List<QhMapFilesEntity> searchMapFilesUnSaved(QhMapFilesQueryParams queryParams) {

        List<QhMapFilesEntity> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();

            String tableName = "qh_map_files";

            StringBuffer sql = new StringBuffer("select * from " + tableName + " ");

            StringBuilder whereSql = new StringBuilder(" where 1=1 ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
                whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
                whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
                whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
            } else {
                whereSql.append(" and (area IS NULL OR LENGTH(area)=0) ");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getAffiliatedFileName())) {
                whereSql.append(" and affiliated_file_name ='" + queryParams.getAffiliatedFileName() + "'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getIsSave())) {
                whereSql.append(" and is_save ='" + queryParams.getIsSave() + "'");
            }else{
                //whereSql.append(" and is_save = '0' ");//未保存
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getIsDelete())) {
                whereSql.append(" and is_delete = '" + queryParams.getIsDelete() + "'");
            }else{
                whereSql.append(" and is_delete = '0' ");//未删除
            }
            //上传类型（SITE_）
            if(!PlatformObjectUtils.isEmpty(queryParams.getType())) {
                whereSql.append(" and type = '" + queryParams.getType() + "'");
            }

            String sql_str = sql.append(whereSql.toString()).toString();
            ps = conn.prepareStatement(sql_str);
            rs = ps.executeQuery();
            while (rs.next()) {
                QhMapFilesEntity entity = new QhMapFilesEntity();
                String uuid = rs.getString("uuid");
                entity.setUuid(uuid);
                String filepath = rs.getString("filepath");
                entity.setFilepath(filepath);
                String name = rs.getString("affiliated_file_name");
                entity.setAffiliatedFileName(name);
                Date create_time = rs.getDate("create_time");
                entity.setCreateTime(create_time);
                result.add(entity);
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return result;
    }

    /**
     * sql 批量更新为已保存状态1
     *
     * @return
     */
    public int sqlBatchUpdateEntities(List<QhMapFilesEntity> list) throws Exception {
        int count = 0;

        /**
         * 获取数据库连接
         */
        Connection conn = getConnection();

        String tableName = "qh_map_files";

        StringBuffer sql = new StringBuffer("UPDATE " + tableName + " set is_save = '1',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");
        //上传文件信息数据
        for (QhMapFilesEntity entity : list) {
            count++;
            String id = entity.getUuid();
            sql.append("'" + id + "',");
        }
        String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
        PreparedStatement ps = conn.prepareStatement(sql_str);
        //ps.executeUpdate(sql_str);
        ps.executeUpdate();
        try {
            if (ps != null) {
                // 关闭连接
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        }
        return count;
    }

    /**
     * sql 批量更新为正在保存状态2
     *
     * @return
     */
    public int sqlBatchUpdateNowEntities(List<QhMapFilesEntity> list) throws Exception {
        int count = 0;

        /**
         * 获取数据库连接
         */
        Connection conn = getConnection();

        String tableName = "qh_map_files";

        StringBuffer sql = new StringBuffer("UPDATE " + tableName + " set is_save = '2',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");
        //上传文件信息数据
        for (QhMapFilesEntity entity : list) {
            count++;
            String id = entity.getUuid();
            sql.append("'" + id + "',");
        }
        String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
        PreparedStatement ps = conn.prepareStatement(sql_str);
        //ps.executeUpdate(sql_str);
        ps.executeUpdate();
        try {
            if (ps != null) {
                // 关闭连接
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        }
        return count;
    }
}
